package com.example.bbook.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.bbook.entity.OrderEntity;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface OrderMapper extends BaseMapper<OrderEntity> {
    @Select({"select o.*, g.username from (select user_id,count(*) " +
            "as num from `u_order` group by user_id) as o , " +
            "s_user as g where g.id = o.user_id order by num DESC limit 1;\n"})
    Map<String, Object> getMaxNumUser();

    @Select({"select o.*, s.`title`, g.name from (select goods_id," +
            "count(*) as num from `u_order` group by goods_id) as" +
            " o , u_goods as g, `g_species` as s where " +
            "g.id = o.goods_id and s.id = g.`species_id` order by num DESC limit 1;"})
    Map<String, Object> getMaxGoodsType();

    @Select({"select o.*, g.name from (select goods_id," +
            "count(*) as num from `u_order` group by goods_id) " +
            "as o , u_goods as g where g.id = o.goods_id order by num DESC limit 1;"})
    Map<String, Object> getMaxNumGoods();

    @Select({"select o.*, g.username from (select user_id," +
            "sum(price) as num from `u_order` group by user_id) as " +
            "o , s_user as g where g.id = o.user_id order by num DESC limit 1;"})
    Map<String, Object> getMaxMoneyUser();

    @Select({"select o.*, g.name from (select goods_id,count(*) " +
            "as num from `g_eva` group by goods_id) as o , u_goods as g " +
            "where g.id = o.goods_id order by num DESC limit 1;"})
    Map<String, Object> getMaxEvaGoods();


    @Select({"select g1.title, " +
            "sum(g2.total) as total " +
            "from `g_species` as g1," +
            "(select s.`title`,s.`name`,s.`parent_id`, " +
            "sum(g.num) as total from (select * " +
            "from `g_species` where `dict_type`='goods') " +
            "as s, (select species_id, count(species_id) " +
            "as num from u_goods group by `species_id`) as " +
            "g where s.`id` = g.`species_id` group by s.title," +
            " s.`name`,s.`parent_id`) as g2 where " +
            "g1.id=g2.parent_id group by g1.title"})
    List<Map<String, Object>> getNumGoodsType();

    @Select({"select s1.title,sum(g2.total) " +
            "as total from `g_species` as s1, " +
            "(select sum(g1.num) as total, " +
            "s.`parent_id` from `g_species` " +
            "as s, (select g.`species_id`, o.* from u_goods as g, " +
            "(select goods_id, sum(price) as num from " +
            "u_order group by goods_id) as o " +
            "where g.id=o.goods_id) as " +
            "g1 where s.id=g1.species_id group " +
            "by s.`parent_id`) as g2 where " +
            "s1.id=g2.parent_id group by s1.title;"})
    List<Map<String, Object>> getMoneyGoodsType();


    @Select({"select s1.title as city, s2.num as total,s2.title " +
            "as goods from `g_species` as s1, (select s.title, " +
            "s.parent_id,g1.num as num from `g_species` " +
            "as s,(select g.`species_id`, sum(o.num) as num " +
            "from `u_goods` as g, (select goods_id, sum(goods_num) " +
            "as num from u_order group by goods_id) as o where " +
            "g.id=o.goods_id group by g.`species_id`) as g1 where " +
            "s.id=g1.`species_id`) as s2 where s1.`id`=s2.parent_id "})
    List<Map<String, Object>> getGoodsRank();

}
